package com.leyou.statis.mapper;

import com.leyou.common.bean.IBaseMapper;
import com.leyou.statis.pojo.Statis;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

public interface StatisMapper extends IBaseMapper<Statis> {

    @Select("SELECT id,num,total_price as totalPrice FROM tb_statis WHERE spu_id = #{spuId} AND create_date = DATE_FORMAT(#{date},'%Y-%m-%d')\n")
    List<Statis> selectStatisByDate(@Param("spuId") Long spuId, @Param("date") Date date);

    @Select("SELECT \n" +
            "\tSUM(num) AS num,\n" +
            "\tSUM(total_price) AS totalPrice,\n" +
            "\tspu_name as spuName\n" +
            "FROM tb_statis\n" +
            "WHERE year(create_date) = #{curYear} and month(create_date)=#{curMonth}\n" +
            "GROUP BY\n" +
            "\tspu_id\n" +
            "ORDER BY\n" +
            "\tnum DESC LIMIT #{total}\n")
    List<Statis> selectStatisBySpu(@Param("curYear") Integer curYear,@Param("curMonth") Integer curMonth,@Param("total") Integer total);

    @Select("SELECT \n" +
            "\tSUM(num) AS num,\n" +
            "\tSUM(total_price) AS totalPrice,\n" +
            "\tcategory_name as categoryName\n" +
            "FROM tb_statis\n" +
            "WHERE year(create_date) = #{curYear} and month(create_date)=#{curMonth}\n" +
            "GROUP BY\n" +
            "\tc_id\n" +
            "ORDER BY\n" +
            "\tnum DESC LIMIT #{total}\n")
    List<Statis> selectStatisByCategory(@Param("curYear") Integer curYear,@Param("curMonth") Integer curMonth,@Param("total") Integer total);


    @Select("SELECT \n" +
            "\tSUM(num) AS num,\n" +
            "\tSUM(total_price) AS totalPrice,\n" +
            "\tquarter(create_date) as quarter\n" +
            "FROM tb_statis\n" +
            "WHERE year(create_date) = #{year} \n" +
            "GROUP BY\n" +
            "\tquarter\n" +
            "ORDER BY\n" +
            "\tquarter asc;")
    List<Statis> selectStatisByQuarter(@Param("year") String year);

    @Select("SELECT \n" +
            "\tSUM(num) AS num,\n" +
            "\tSUM(total_price) AS totalPrice,\n" +
            "\tmonth(create_date) as month\n" +
            "FROM tb_statis\n" +
            "WHERE year(create_date) = #{year} \n" +
            "GROUP BY\n" +
            "\tmonth\n" +
            "ORDER BY\n" +
            "\tmonth asc")
    List<Statis> selectStatisByMonth(@Param("year") String year);

    @Select("SELECT \n" +
            "\tSUM(num) AS num,\n" +
            "\tSUM(total_price) AS totalPrice,\n" +
            "\tmonth(create_date) as month,\n" +
            "\tyear(create_date) as year\n" +
            "FROM tb_statis\n" +
            "WHERE year(create_date) in (${years})\n" +
            "GROUP BY\n" +
            "\tyear,month\n" +
            "ORDER BY\n" +
            "\tyear,month asc")
    List<Statis> selectStatisByYears(@Param("years") String years);

}